﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using Sector7.Framework.EntityType;
using Sector7.Framework.DAO;
using Sector7.Framework.DAO.Standard;
using Sector7.Framework.Utility;
using System.IO;
using Sector7.Framework.DB;
using Sector7.Framework.Core;
using System.Collections;
using Sector7.Framework.Enums;

namespace Sector7.Framework.Services
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码和配置文件中的类名“CoreService”。
    public class CoreService : ICoreService
    {
        const int SUCCESS = 1;

        #region /// System

        public int InitializeDatabase()
        {
            string sqlPath = PathUtil.Combine(ApplicationUtil.GetBaseDirectory(), "App_Data");

            StringBuilder sb = new StringBuilder();
            StreamReader reader = new StreamReader(string.Format(@"{0}\{1}", sqlPath, "Default.sql"));
            while (!reader.EndOfStream)
            {
                sb.AppendLine(reader.ReadLine());
            }

            reader.Close();
            reader.Dispose();

            return DAOFactory.GetDAO<DatabaseDAO>().CreateTable(sb.ToString());
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int CreateTable(string connectionString, string sql)
        {
            return DAOFactory.GetDAO<DatabaseDAO>().CreateTable(sql);
        }

        /// <summary>
        /// 数据迁移
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="datas">插入的数据</param>
        /// <returns></returns>
        public int InsertDatasToTable(string connectionString, List<S7_DatabaseTable> datas)
        {
            string sql = null;

            foreach (var table in datas)
            {
                //1:判断表是否存在,如果存在则删除,否则创建表
                sql = string.Format(@"
                    IF EXISTS (
	                    SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND
	                    type in (N'U')
                    ) DROP TABLE [dbo].[{0}]", table.TableName);

                //执行sql语句,helper只在当前语句块生效
                using (DBHelper helper = new DBHelper(connectionString))
                {
                    helper.ExecuteNonQuery(sql);
                }

                //2：创建要映射的表
                sql = string.Format(@"
                    CREATE TABLE [dbo].[{0}](
                            [Value] [nvarchar](16) NOT NULL,
                            [Implication] [nvarchar](128) NOT NULL,
                            [Description] [nvarchar](512) NULL,
	                        CONSTRAINT [PK_{0}]
	                    PRIMARY KEY CLUSTERED (
		                    [Value] ASC
	                    ) WITH (
		                    PAD_INDEX = OFF,
		                    STATISTICS_NORECOMPUTE = OFF,
		                    IGNORE_DUP_KEY = OFF,
		                    ALLOW_ROW_LOCKS = ON,
		                    ALLOW_PAGE_LOCKS = ON
	                    ) ON [PRIMARY]
                    ) ON [PRIMARY]",
                    table.TableName);

                CreateTable(connectionString, sql);
                //3:插入数据到M7_ValueDomainMapping
                sql = @"INSERT INTO M7_ValueDomainMapping
                                (TableName
                                ,ValueDomainId)
                            VALUES
                                (@TableName
                                ,@ValueDomainId)";

                Log.WriteLine(LogSeverityType.DBG, "COS", sql);

                using (DBHelper helper = new DBHelper(connectionString))
                {
                    helper.ExecuteNonQuery(sql, 
                        DBUtil.CreateSqlParameter(table.TableName, "TableName"),
                        DBUtil.CreateSqlParameter(table.ValueDomainId, "ValueDomainId"));
                }

                //4.将对应值域的所有值插入到新建表
                sql = string.Format(@"INSERT INTO {0}
                                (Value
                                ,Implication
                                ,Description)
                            VALUES
                                (@DomainItemValue
                                ,@DomainItemImplication
                                ,@DomainItemDescription)",
                                table.TableName);

                Log.WriteLine(LogSeverityType.DBG, "COS", sql);
                //根据值域Id读取所有已经存在的值
                List<S7_ValueDomainItem> items = DAOFactory.GetDAO<ValueDomainItemDAO>().
                                                                ReadAll(table.ValueDomainId);

                using (DBHelper helper = new DBHelper(connectionString))
                {
                    helper.ExecuteBatchNonQuery(sql, new ArrayList(items),
                        "DomainItemValue", "DomainItemImplication", "DomainItemDescription");
                }
            }

            return 0;
        }

        #endregion

        #region /// DataElement
        
        /// <summary>
        /// 添加数据元
        /// </summary>
        /// <param name="dataElement">数据元</param>
        /// <returns>操作结果</returns>
        public object AddDataElement(S7_DataElement dataElement)
        {
            return DAOFactory.GetDAO<DataElementDAO>().Create(dataElement);
        }

        /// <summary>
        /// 根据数据分组获取所有数据元
        /// </summary>
        /// <param name="groupId">数据分组编号</param>
        /// <returns>数据元列表</returns>
        public List<S7_DataElement> GetAllDataElementsByGroupId(int groupId)
        {
            return DAOFactory.GetDAO<DataElementDAO>().ReadAll(groupId);
        }

        /// <summary>
        /// 修改数据元
        /// </summary>
        /// <param name="delta">数据元改变量</param>
        /// <returns>操作结果</returns>
        public int SetDataElement(EntityDeltaBase delta)
        {
            return DAOFactory.GetDAO<DataElementDAO>().Update(delta);
        }

        /// <summary>
        /// 删除值域项
        /// </summary>
        /// <param name="domainItemId">值域项编号</param>
        /// <returns>操作结果</returns>
        public int DeleteDataElement(string elementId)
        {
            return DAOFactory.GetDAO<DataElementDAO>().Delete(elementId);
        }

        #endregion

        #region /// ValueType

        /// <summary>
        /// 添加数据元值类型
        /// </summary>
        /// <param name="valueType">数据元值类型</param>
        /// <returns>数据元值类型主键</returns>
        public object AddValueType(S7_ValueType valueType)
        {
            return DAOFactory.GetDAO<ValueTypeDAO>().Create(valueType);
        }

        /// <summary>
        /// 获取所有数据元值类型
        /// </summary>
        /// <returns>数据元值类型列表</returns>
        public List<S7_ValueType> GetAllValueTypes()
        {
            return DAOFactory.GetDAO<ValueTypeDAO>().ReadAll();
        }

        /// <summary>
        /// 修改数据元值类型
        /// </summary>
        /// <param name="delta">数据元值类型改变量</param>
        /// <returns>操作结果</returns>
        public int SetValueType(EntityDeltaBase delta)
        {
            return DAOFactory.GetDAO<ValueTypeDAO>().Update(delta);
        }

        #endregion

        #region /// ValueDomain

        /// <summary>
        /// 添加数据元值域
        /// </summary>
        /// <param name="valueDomain">数据元值域</param>
        /// <returns>操作结果</returns>
        public object AddValueDomain(S7_ValueDomain valueDomain)
        {
            DAOFactory.GetDAO<ValueDomainDAO>().Create(valueDomain);
            DAOFactory.GetDAO<ValueDomainItemDAO>().Create(valueDomain.Items);

            return SUCCESS;
        }

        /// <summary>
        /// 获取所有数据元值域（不包含子集）
        /// </summary>
        /// <returns>数据元值域列表</returns>
        public List<S7_ValueDomain> GetAllValueDomains()
        {
            return DAOFactory.GetDAO<ValueDomainDAO>().ReadAll();
        }

        /// <summary>
        /// 根据数据分组编号获取所有数据元值域
        /// </summary>
        /// <param name="groupId">数据分组编号</param>
        /// <returns>数据元值域列表</returns>
        public List<S7_ValueDomain> GetAllValueDomainsByGroupId(int groupId)
        {
            return DAOFactory.GetDAO<ValueDomainDAO>().ReadAll(groupId);
        }

        /// <summary>
        /// 更新值域
        /// </summary>
        /// <param name="delta">值域改变量</param>
        /// <returns>操作结果</returns>
        public int SetValueDomain(EntityDeltaBase delta)
        {
            return DAOFactory.GetDAO<ValueDomainDAO>().Update(delta);
        }

        /// <summary>
        /// 删除值域
        /// </summary>
        /// <param name="domainId">值域编号</param>
        /// <returns>操作结果</returns>
        public int DeleteValueDomain(string domainId)
        {
            return DAOFactory.GetDAO<ValueDomainDAO>().Delete(domainId);
        }

        #endregion

        #region /// ValueDomainItem

        /// <summary>
        /// 添加值域项
        /// </summary>
        /// <param name="domainItem">值域项</param>
        /// <returns>值域项编号</returns>
        public object AddValueDomainItem(S7_ValueDomainItem domainItem)
        {
            return DAOFactory.GetDAO<ValueDomainItemDAO>().Create(domainItem);
        }

        /// <summary>
        /// 根据值域编号获取所有数据元值域项
        /// </summary>
        /// <param name="domainId">值域编号</param>
        /// <returns></returns>
        public List<S7_ValueDomainItem> GetAllValueDomainItemsByDomainId(string domainId)
        {
            return DAOFactory.GetDAO<ValueDomainItemDAO>().ReadAll(domainId);
        }

        /// <summary>
        /// 更新值域项
        /// </summary>
        /// <param name="delta">值域项改变量</param>
        /// <returns></returns>
        public int SetValueDomainItem(EntityDeltaBase delta)
        {
            return DAOFactory.GetDAO<ValueDomainItemDAO>().Update(delta);
        }

        /// <summary>
        /// 删除值域项
        /// </summary>
        /// <param name="domainItemId">值域项编号</param>
        /// <returns>操作结果</returns>
        public int DeleteValueDomainItem(object domainItemId)
        {
            return DAOFactory.GetDAO<ValueDomainItemDAO>().Delete(domainItemId);
        }

        #endregion

        #region /// DataTemplate

        /// <summary>
        /// 添加数据模板
        /// </summary>
        /// <param name="dataTemplate">数据模板</param>
        /// <returns>数据模板主键</returns>
        public object AddDataTemplate(S7_DataTemplate dataTemplate)
        {
            //1.添加数据模板到S7_DataTemplate
            object result = DAOFactory.GetDAO<DataTemplateDAO>().Create(dataTemplate);

            //2.根据列名生成相应的字段及其属性
            int templateId = Convert.ToInt32(result);
            string columns = string.Empty;
            bool isNumberic = false; 

            dataTemplate.Items.ForEach(delegate(S7_DataTemplateItem item)
            {
                isNumberic = false;
                item.TemplateId = templateId;
                string format = string.Empty;
                string[] headers = new string[]{"AN","N","A"};
                string separatePoint = "..";
                string separateComma = ",";
                string __format = item.Metadata.ElementFormat;
                switch (item.Metadata.ValueType.TypeIdentifier)
                {
                    case "S":
                        //解析字符串，判断..
                        //以..分割
                        foreach (var header in headers)
                        {
                            if (__format.Contains(header))
                            {
                                //去掉字母，开始判断长度，字符串类型不存在小数点
                                __format = __format.Replace(header, string.Empty);
                                int pointSite = __format.IndexOf(separatePoint);
                                if (pointSite != -1)
                                {
                                    __format = __format.Substring(pointSite + 2);
                                }
                                break;
                            }
                        }
                        format = string.Format("{0}{1}", "nvarchar", __format);
                        break;
                    case "N":
                        string dataType = "int";
                        //解析字符串，判断..
                        //以..分割，判断是否有","再以","分割
                        foreach( var header in headers)
                        {
                            if(__format.Contains(header)) 
                            {
                                //去掉字母，开始判断长度
                                __format = __format.Replace(header,string.Empty);
                                int pointSite = __format.IndexOf(separatePoint);
                                if(pointSite!=-1)
                                {
                                    __format = __format.Substring(pointSite+2);
                                }
                                //判断逗号位置
                                if (__format.IndexOf(separateComma) != -1)
                                {
                                    dataType = "decimal";
                                }
                                else
                                {
                                    isNumberic = true;
                                }

                                break;
                            }
                        }
                        //format = string.Format("{0}({1})",dataType,__format);
                        format = string.Format("{0}", dataType);
                        break;
                    default:
                        format = ConvertUtil.ValueTypeToDBType(item.Metadata.ValueType.TypeIdentifier);
                        break;
                }
                string specialMark = string.Empty;
                string isNull = "NULL";

                if(item.Metadata.KeyType == Convert.ToInt32(DBFieldType.PrimaryKey))
                {
                    if (item.Metadata.isAuto && isNumberic) 
                    {
                        specialMark = "IDENTITY(1,1)";
                    }
                    isNull = "NOT NULL";
                }
                columns += string.Format("{0} {1} {2} {3},", item.Metadata.ColumnName, format, specialMark, isNull);
            });
            DAOFactory.GetDAO<DataTemplateItemDAO>().Create(dataTemplate.Items);

            //3.创建对应的表
            string sql = string.Format(@"CREATE TABLE [dbo].[{0}]({1}
                                CONSTRAINT [PK_{0}] 
                                PRIMARY KEY CLUSTERED([{2}] ASC
                                )WITH (PAD_INDEX  = OFF, 
                                STATISTICS_NORECOMPUTE  = OFF, 
                                IGNORE_DUP_KEY = OFF, 
                                ALLOW_ROW_LOCKS  = ON, 
                                ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                                ) ON [PRIMARY]", 
                                dataTemplate.TableName, 
                                columns, 
                                dataTemplate.Items[0].Metadata.ColumnName);

            
            DAOFactory.GetDAO<DatabaseDAO>().CreateTable(sql);

            return templateId;
        }

        /// <summary>
        /// 获取所有数据模板
        /// </summary>
        /// <returns>数据模板列表</returns>
        public List<S7_DataTemplate> GetAllDataTemplates()
        {
            return DAOFactory.GetDAO<DataTemplateDAO>().ReadAll();
        }

        /// <summary>
        /// 删除当前数据模板项
        /// </summary>
        /// <param name="templateId">数据模板项编号</param>
        /// <returns></returns>
        public int DeleteDataTemplate(int templateId)
        {
            return DAOFactory.GetDAO<DataTemplateDAO>().Delete(templateId);
        }
        #endregion

        #region /// DataTemplateItem

        /// <summary>
        /// 根据数据模板编号获取所有数据模板项列表
        /// </summary>
        /// <param name="templateId">数据模板编号</param>
        /// <returns>数据模板项列表</returns>
        public List<S7_DataTemplateItem> GetAllDataTemplateItemsByTemplateId(int templateId)
        {
            return DAOFactory.GetDAO<DataTemplateItemDAO>().ReadAll(templateId);
        }

        #endregion

        #region /// Metadata

        /// <summary>
        /// 添加内部数据元
        /// </summary>
        /// <param name="metadata">内部数据元</param>
        /// <returns>操作结果</returns>
        public object AddMetadata(S7_Metadata metadata)
        {
            return DAOFactory.GetDAO<MetadataDAO>().Create(metadata);
        }

        public List<S7_Metadata> GetAllMetadatas()
        {
            return DAOFactory.GetDAO<MetadataDAO>().ReadAll();
        }

        /// <summary>
        /// 根据数据分组获取所有内部数据元
        /// </summary>
        /// <param name="groupId">数据分组编号</param>
        /// <returns>内部数据元列表</returns>
        public List<S7_Metadata> GetAllMetadatasByGroupId(int groupId)
        {
            return DAOFactory.GetDAO<MetadataDAO>().ReadAll(groupId);
        }

        /// <summary>
        /// 修改内部数据元
        /// </summary>
        /// <param name="delta">内部数据元改变量</param>
        /// <returns>操作结果</returns>
        public int SetMetadata(EntityDeltaBase delta)
        {
            return DAOFactory.GetDAO<MetadataDAO>().Update(delta);
        }

        #endregion

        #region /// DataGroup

        /// <summary>
        /// 添加数据分组
        /// </summary>
        /// <param name="dataGroup">数据分组</param>
        /// <returns>数据分组编号</returns>
        public object AddDataGroup(S7_DataGroup dataGroup)
        {
            return DAOFactory.GetDAO<DataGroupDAO>().Create(dataGroup);
        }

        /// <summary>
        /// 获取所有数据分组
        /// </summary>
        /// <returns>数据分组列表</returns>
        public List<S7_DataGroup> GetAllDataGroups()
        {
            return DAOFactory.GetDAO<DataGroupDAO>().ReadAll();
        }

        /// <summary>
        /// 根据类型获取所有数据分组
        /// </summary>
        /// <param name="typeId">数据分组类型</param>
        /// <returns>数据分组列表</returns>
        public List<S7_DataGroup> GetAllDataGroupsByTypeId(int typeId)
        {
            return DAOFactory.GetDAO<DataGroupDAO>().ReadAll(typeId);
        }

        /// <summary>
        /// 修改数据分组
        /// </summary>
        /// <param name="delta">数据分组改变量</param>
        /// <returns>操作结果</returns>
        public int SetDataGroup(EntityDeltaBase delta)
        {
            return DAOFactory.GetDAO<DataGroupDAO>().Update(delta);
        }
        
        /// <summary>
        /// 根据分组标识符获取数据分组
        /// </summary>
        /// <param name="groupIdentifier">分组标识符</param>
        /// <returns>数据分组</returns>
        public S7_DataGroup GetDataGroupByIdentifier(string groupIdentifier)
        {
            return DAOFactory.GetDAO<DataGroupDAO>().Read(groupIdentifier);
        }

        #endregion
    }
}
